package org.bonorsoft.modules.education.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;
import org.bonorsoft.modules.education.entity.EduInfor;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.bonorsoft.modules.statistical.entity.*;
import org.bonorsoft.modules.statistical.out.*;

/**
 * @Description: 全市学校信息表
 * @Author: WilliamYe
 * @Date:   2020-11-23
 * @Version: V1.0
 */
public interface EduInforMapper extends BaseMapper<EduInfor> {

    @Select("select sum(e.TWENTY_FOUR_YEARS) twenty_four," +
            "sum(e.TWENTY_FIVE_YEARS) twenty_five," +
            "sum(e.THIRTY_YEARS) thirty," +
            "sum(e.THIRTY_FIVE_YEARS) thirty_five," +
            "sum(e.FORTY_YEARS) forty," +
            "sum(e.FORTY_FIVE_YEARS) forty_five," +
            "sum(e.FIFTY_YEARS) fifty," +
            "sum(e.FIFTY_FIVE_YEARS) fifty_five," +
            "sum(e.SIXTY_YEARS) sixty," +
            "sum(e.TOTAL_NUMBER_STAFF) total_staff," +
            "sum(e.TOTAL_STUDENT) total_student," +
            "sum(e.GRADUATE_DEGREE_STAFF) graduate_staff," +
            "sum(e.BACHELOR_DEGREE_STAFF) bachelor_staff," +
            "sum(e.JUNIOR_COLLEGE_DEGREE_STAFF) junior_staff," +
            "sum(e.HIGH_SCHOOL_EDUCATION_STAFF) high_staff," +
            "sum(e.BELOW_HIGH_SCHOOL_STAFF) below_staff," +
            "sum(e.SCHOOL_NUMBER) school_number," +
            "e.SCHOOL_LOCATION_THREE " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year} " +
            "GROUP BY e.SCHOOL_LOCATION_THREE")
    List<StatisticalBasicData> getAllAgeStructure(Integer year);

    @Select("select sum(e.TWENTY_FOUR_YEARS) twenty_four," +
            "sum(e.TWENTY_FIVE_YEARS) twenty_five," +
            "sum(e.THIRTY_YEARS) thirty," +
            "sum(e.THIRTY_FIVE_YEARS) thirty_five," +
            "sum(e.FORTY_YEARS) forty," +
            "sum(e.FORTY_FIVE_YEARS) forty_five," +
            "sum(e.FIFTY_YEARS) fifty," +
            "sum(e.FIFTY_FIVE_YEARS) fifty_five," +
            "sum(e.SIXTY_YEARS) sixty," +
            "sum(e.TOTAL_NUMBER_STAFF) total_staff," +
            "sum(e.TOTAL_STUDENT) total_student," +
            "sum(e.GRADUATE_DEGREE_STAFF) graduate_staff," +
            "sum(e.BACHELOR_DEGREE_STAFF) bachelor_staff," +
            "sum(e.JUNIOR_COLLEGE_DEGREE_STAFF) junior_staff," +
            "sum(e.HIGH_SCHOOL_EDUCATION_STAFF) high_staff," +
            "sum(e.BELOW_HIGH_SCHOOL_STAFF) below_staff," +
            "sum(e.SCHOOL_NUMBER) school_number," +
            "e.SCHOOL_LOCATION_THREE " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year} " +
            "and e.LEARN_TYPE = #{type}  " +
            "GROUP BY e.SCHOOL_LOCATION_THREE")
    List<StatisticalBasicData> getAllAgeStructureByType(Integer year, String type);

    @Select("select SCHOOL_NAME," +
            "SCHOOL_TYPE," +
            "TOTAL_AREA_SCHOOL," +
            "SCHOOL_ADDRESS," +
            "TOTAL_STUDENT," +
            "TOTAL_NUMBER_STAFF," +
            "LONGITUDE_X," +
            "LONGITUDE_Y," +
            "SCHOOL_LOCATION_TWO," +
            "SCHOOL_LOCATION_THREE, " +
            "SCHOOL_LOCATION_FOUR, " +
            "SCHOOL_LOCATION_FIVE " +
            "from EDU_SCHOOL_INFORMATION " +
            "where STATISTICAL_YEAR = #{year}")
    List<EduInforVo> getEduInforVo(Integer year);

    @Select("select SCHOOL_NAME," +
            "SCHOOL_TYPE," +
            "TOTAL_AREA_SCHOOL," +
            "SCHOOL_ADDRESS," +
            "TOTAL_STUDENT," +
            "TOTAL_NUMBER_STAFF," +
            "LONGITUDE_X," +
            "LONGITUDE_Y," +
            "SCHOOL_LOCATION_TWO," +
            "SCHOOL_LOCATION_THREE, " +
            "SCHOOL_LOCATION_FOUR, " +
            "SCHOOL_LOCATION_FIVE " +
            "from EDU_SCHOOL_INFORMATION " +
            "where STATISTICAL_YEAR = #{year} " +
            "and LEARN_TYPE = #{type}")
    List<EduInforVo> getEduInforVoByType(Integer year, String type);

    @Select("select sum(e.MORALITY_LIFE_STAFF) MORALITY_LIFE_STAFF," +
            "sum(e.LANGUAGE_STAFF) LANGUAGE_STAFF," +
            "sum(e.MATHEMATICS_STAFF) MATHEMATICS_STAFF," +
            "sum(e.ENGLISH_LANGUAGE_STAFF) ENGLISH_LANGUAGE_STAFF," +
            "sum(e.JAPANESE_LANGUAGE_STAFF) JAPANESE_LANGUAGE_STAFF," +
            "sum(e.RUSSIAN_LANGUAGE_STAFF) RUSSIAN_LANGUAGE_STAFF," +
            "sum(e.PHYSICAL_STAFF) PHYSICAL_STAFF," +
            "sum(e.CHEMICAL_STAFF) CHEMICAL_STAFF," +
            "sum(e.BIOLOGICAL_STAFF) BIOLOGICAL_STAFF," +
            "sum(e.GEOGRAPHIC_STAFF) GEOGRAPHIC_STAFF," +
            "sum(e.HISTORY_STAFF) HISTORY_STAFF," +
            "sum(e.TECHNOLOGY_STAFF) TECHNOLOGY_STAFF," +
            "sum(e.GENERAL_STAFF) GENERAL_STAFF," +
            "sum(e.EDUCATION_STAFF) EDUCATION_STAFF," +
            "sum(e.ART_STAFF) ART_STAFF," +
            "sum(e.MUSIC_STAFF) MUSIC_STAFF," +
            "sum(e.FINE_ARTS_STAFF) FINE_ARTS_STAFF," +
            "sum(e.PRACTICE_ACTIVITIES_STAFF) PRACTICE_ACTIVITIES_STAFF," +
            "sum(e.OTHER_STAFF) OTHER_STAFF " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year}")
   SubjectVo getSubjectTeachers(Integer year);

    @Select("select sum(e.MORALITY_LIFE_STAFF) MORALITY_LIFE_STAFF," +
            "sum(e.LANGUAGE_STAFF) LANGUAGE_STAFF," +
            "sum(e.MATHEMATICS_STAFF) MATHEMATICS_STAFF," +
            "sum(e.ENGLISH_LANGUAGE_STAFF) ENGLISH_LANGUAGE_STAFF," +
            "sum(e.JAPANESE_LANGUAGE_STAFF) JAPANESE_LANGUAGE_STAFF," +
            "sum(e.RUSSIAN_LANGUAGE_STAFF) RUSSIAN_LANGUAGE_STAFF," +
            "sum(e.PHYSICAL_STAFF) PHYSICAL_STAFF," +
            "sum(e.CHEMICAL_STAFF) CHEMICAL_STAFF," +
            "sum(e.BIOLOGICAL_STAFF) BIOLOGICAL_STAFF," +
            "sum(e.GEOGRAPHIC_STAFF) GEOGRAPHIC_STAFF," +
            "sum(e.HISTORY_STAFF) HISTORY_STAFF," +
            "sum(e.TECHNOLOGY_STAFF) TECHNOLOGY_STAFF," +
            "sum(e.GENERAL_STAFF) GENERAL_STAFF," +
            "sum(e.EDUCATION_STAFF) EDUCATION_STAFF," +
            "sum(e.ART_STAFF) ART_STAFF," +
            "sum(e.MUSIC_STAFF) MUSIC_STAFF," +
            "sum(e.FINE_ARTS_STAFF) FINE_ARTS_STAFF," +
            "sum(e.PRACTICE_ACTIVITIES_STAFF) PRACTICE_ACTIVITIES_STAFF," +
            "sum(e.OTHER_STAFF) OTHER_STAFF " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year} " +
            "and e.LEARN_TYPE = #{type}")
    SubjectVo getSubjectTeachersByType(Integer year, String type);

    @Select("SELECT sum(e.IN_SCHOOL_NUMBER) IN_SCHOOL_NUMBER," +
            "sum(e.SCHOOL_NUMBER) SCHOOL_NUMBER," +
            "e.COUNTY," +
            "e.STATISTICAL_YEAR " +
            "FROM EDU_STUDENT_NUMBER e " +
            "GROUP BY e.COUNTY,e.STATISTICAL_YEAR " +
            "ORDER BY e.COUNTY asc ")
    List<FiveStudentVo> getAllFiveStudent();

    @Select("SELECT sum(e.IN_SCHOOL_NUMBER) IN_SCHOOL_NUMBER," +
            "sum(e.SCHOOL_NUMBER) SCHOOL_NUMBER," +
            "e.COUNTY," +
            "e.STATISTICAL_YEAR " +
            "FROM EDU_STUDENT_NUMBER e " +
            "where e.LEARN_TYPE = #{type} " +
            "GROUP BY e.COUNTY,e.STATISTICAL_YEAR " +
            "ORDER BY e.COUNTY asc ")
    List<FiveStudentVo> getAllFiveStudentByType(String type);

    @Select("SELECT TOTAL_STUDENT," +
            "TOTAL_NUMBER_STAFF," +
            "SCHOOL_NAME," +
            "SCHOOL_LOCATION_THREE," +
            "LEARN_TYPE " +
            "from EDU_SCHOOL_INFORMATION " +
            "WHERE LEARN_TYPE in (1,2,3) " +
            "AND STATISTICAL_YEAR = #{year}  " +
            "ORDER BY LEARN_TYPE, SCHOOL_LOCATION_THREE ASC")
    List<TeacherStudentRatioVo> getTeacherStudentRatio(Integer year);

    @Select("select sum(e.TWENTY_FOUR_YEARS) twenty_four," +
            "sum(e.TWENTY_FIVE_YEARS) twenty_five," +
            "sum(e.THIRTY_YEARS) thirty," +
            "sum(e.THIRTY_FIVE_YEARS) thirty_five," +
            "sum(e.FORTY_YEARS) forty," +
            "sum(e.FORTY_FIVE_YEARS) forty_five," +
            "sum(e.FIFTY_YEARS) fifty," +
            "sum(e.FIFTY_FIVE_YEARS) fifty_five," +
            "sum(e.SIXTY_YEARS) sixty," +
            "sum(e.TOTAL_NUMBER_STAFF) total_staff," +
            "sum(e.TOTAL_STUDENT) total_student," +
            "sum(e.GRADUATE_DEGREE_STAFF) graduate_staff," +
            "sum(e.BACHELOR_DEGREE_STAFF) bachelor_staff," +
            "sum(e.JUNIOR_COLLEGE_DEGREE_STAFF) junior_staff," +
            "sum(e.HIGH_SCHOOL_EDUCATION_STAFF) high_staff," +
            "sum(e.BELOW_HIGH_SCHOOL_STAFF) below_staff," +
            "sum(e.SCHOOL_NUMBER) school_number," +
            "e.SCHOOL_LOCATION_THREE " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year} " +
            "GROUP BY e.SCHOOL_LOCATION_THREE")
    List<StatisticalBasicData> getAllStructure(Integer year);

    @Select("select sum(e.TWENTY_FOUR_YEARS) twenty_four," +
            "sum(e.TWENTY_FIVE_YEARS) twenty_five," +
            "sum(e.THIRTY_YEARS) thirty," +
            "sum(e.THIRTY_FIVE_YEARS) thirty_five," +
            "sum(e.FORTY_YEARS) forty," +
            "sum(e.FORTY_FIVE_YEARS) forty_five," +
            "sum(e.FIFTY_YEARS) fifty," +
            "sum(e.FIFTY_FIVE_YEARS) fifty_five," +
            "sum(e.SIXTY_YEARS) sixty," +
            "sum(e.TOTAL_NUMBER_STAFF) total_staff," +
            "sum(e.TOTAL_STUDENT) total_student," +
            "sum(e.GRADUATE_DEGREE_STAFF) graduate_staff," +
            "sum(e.BACHELOR_DEGREE_STAFF) bachelor_staff," +
            "sum(e.JUNIOR_COLLEGE_DEGREE_STAFF) junior_staff," +
            "sum(e.HIGH_SCHOOL_EDUCATION_STAFF) high_staff," +
            "sum(e.BELOW_HIGH_SCHOOL_STAFF) below_staff," +
            "sum(e.SCHOOL_NUMBER) school_number," +
            "e.SCHOOL_LOCATION_THREE " +
            "from EDU_SCHOOL_INFORMATION e " +
            "where e.STATISTICAL_YEAR = #{year} " +
            "and e.LEARN_TYPE = #{type}  " +
            "GROUP BY e.SCHOOL_LOCATION_THREE")
    List<StatisticalBasicData> getAllStructureByType(Integer year, String type);

    @Select("SELECT " +
            "e.STATISTICAL_YEAR as year," +
            "e.SCHOOL_LOCATION_THREE as location," +
            "e.LEARN_TYPE as learn," +
            "count( e.SCHOOL_NUMBER ) SCHOOL_NUMBER " +
            "FROM EDU_SCHOOL_INFORMATION e " +
            "GROUP BY " +
            "e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE " +
            "ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<SchoolsNumber> getSchoolsNumber();

    @Select("SELECT " +
            "e.STATISTICAL_YEAR as year," +
            "e.SCHOOL_LOCATION_THREE as location," +
            "e.LEARN_TYPE as learn," +
            "sum(e.GRADUATE_DEGREE_STAFF) graduate_staff," +
            "sum(e.BACHELOR_DEGREE_STAFF) bachelor_staff," +
            "sum(e.JUNIOR_COLLEGE_DEGREE_STAFF) junior_staff," +
            "sum(e.HIGH_SCHOOL_EDUCATION_STAFF) high_staff," +
            "sum(e.BELOW_HIGH_SCHOOL_STAFF) below_staff " +
            "FROM EDU_SCHOOL_INFORMATION e " +
            "GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE " +
            "ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<Qualifications> getQualifications();

    @Select("SELECT " +
            "e.STATISTICAL_YEAR as year," +
            "e.SCHOOL_LOCATION_THREE as location," +
            "e.LEARN_TYPE as learn," +
            "sum(e.TWENTY_FOUR_YEARS) twenty_four," +
            "sum(e.TWENTY_FIVE_YEARS) twenty_five," +
            "sum(e.THIRTY_YEARS) thirty," +
            "sum(e.THIRTY_FIVE_YEARS) thirty_five," +
            "sum(e.FORTY_YEARS) forty," +
            "sum(e.FORTY_FIVE_YEARS) forty_five," +
            "sum(e.FIFTY_YEARS) fifty," +
            "sum(e.FIFTY_FIVE_YEARS) fifty_five," +
            "sum(e.SIXTY_YEARS) sixty " +
            "FROM EDU_SCHOOL_INFORMATION e " +
            "GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE " +
            "ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<AgeStructure> getAgeStructure();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.FULL_SENIOR_STAFF) FULL_SENIOR,sum(e.DEPUTY_SENIOR_STAFF) DEPUTY_SENIOR,sum(e.INTERMEDIATE_LEVEL_STAFF) INTERMEDIATE_LEVEL,sum(e.ASSISTANT_LEVEL_STAFF) ASSISTANT_LEVEL,sum(e.STAFF_LEVEL_STAFF) STAFF_LEVEL,sum(e.UNDETERMINED_RANK_STAFF) UNDETERMINED_RANK FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<TitleStructure> getTitleStructure();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.TOTAL_NUMBER_STAFF) teacher_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<TeacherNumber> getTeacherNumber();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.TOTAL_STUDENT) student_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<InSchoolNumber> getInSchoolNumber();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.TOTAL_STUDENT) student_number,sum(e.TOTAL_NUMBER_STAFF) staff_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<StaffStudentRatio> getStaffStudentRatio();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.MALE_STUDENTS) boy_number,sum(e.WOMAN_STUDENTS) girl_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<BoyGirlRatio> getBoyGirlRatio();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.MORALITY_LIFE_STAFF) MORALITY_LIFE,sum(e.LANGUAGE_STAFF) LANGUAGE,sum(e.MATHEMATICS_STAFF) MATHEMATICS,sum(e.ENGLISH_LANGUAGE_STAFF) ENGLISH,sum(e.JAPANESE_LANGUAGE_STAFF) JAPANESE,sum(e.RUSSIAN_LANGUAGE_STAFF) RUSSIAN,sum(e.PHYSICAL_STAFF) PHYSICAL,sum(e.CHEMICAL_STAFF) CHEMICAL,sum(e.BIOLOGICAL_STAFF) BIOLOGICAL,sum(e.GEOGRAPHIC_STAFF) GEOGRAPHIC,sum(e.HISTORY_STAFF) HISTORY,sum(e.TECHNOLOGY_STAFF) TECHNOLOGY,sum(e.GENERAL_STAFF) GENERAL,sum(e.EDUCATION_STAFF) EDUCATION,sum(e.ART_STAFF) ART,sum(e.MUSIC_STAFF) MUSIC,sum(e.FINE_ARTS_STAFF) FINE_ARTS,sum(e.PRACTICE_ACTIVITIES_STAFF) PRACTICE,sum(e.OTHER_STAFF) OTHER,sum(e.DONT_TEACH_STAFF) DONT_TEACH FROM EDU_SCHOOL_INFORMATION e WHERE e.LEARN_TYPE IN(1,2,3) GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<SubjectTeacher> getSubjectTeacher();

    @Select("SELECT e.STATISTICAL_YEAR+1 as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.TOTAL_GRADUATES) student_number FROM EDU_SCHOOL_INFORMATION e WHERE e.LEARN_TYPE = 3 and e.STATISTICAL_YEAR = #{year}  GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<ExamStudent> getExamStudent(Integer year);

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,sum(e.TOTAL_NUMBER_ADMISSIONS) numbers FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE")
    List<InSchoolStudent> getInSchoolStudent();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.TOTAL_GRADUATES) student_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<GraduationStudent> getGraduationStudent();

    @Select("SELECT e.STATISTICAL_YEAR+1 as year,e.SCHOOL_LOCATION_THREE as location,sum(e.TOTAL_GRADUATES) numbers FROM EDU_SCHOOL_INFORMATION e WHERE e.LEARN_TYPE = 0  GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE")
    List<AgeStudent> getAgeStudent();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,count(0) as school_number FROM EDU_SCHOOL_INFORMATION e WHERE e.hold_type = '民办'  GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<PrivateSchool> getPrivateSchool();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.FULL_TIME_STAFF) as teacher_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<FullTimeFaculty> getFullTimeFaculty();

    @Select("SELECT e.STATISTICAL_YEAR as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.FOREIGN_STUDENT) as student_number FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<ForeignStudent> getForeignStudent();

    @Select("SELECT e.STATISTICAL_YEAR+1 as year,e.SCHOOL_LOCATION_THREE as location,e.LEARN_TYPE as learn,sum(e.CLASSE_NUMBER) as CLASS_NUMBER,sum(e.TOTAL_STUDENT) as TOTAL_STUDENT FROM EDU_SCHOOL_INFORMATION e GROUP BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE ORDER BY e.STATISTICAL_YEAR,e.SCHOOL_LOCATION_THREE,e.LEARN_TYPE")
    List<AverageClass> getAverageClass();

    @Select("SELECT  id,year,location,institutions  FROM EDU_INSTITUTIONS")
    List<InstitutionNumbers> getInstitutionNumbers();
}
